/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package cn.siox.util.database;

import com.db4o.*;
import com.db4o.query.Query;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

/**
 *
 * @author Lindily
 */
public class DatabaseOperator {

    ObjectContainer db2 = Db4o.openFile("./data/" + "encyclopedia" + ".db");
    ObjectContainer db3 = Db4o.openFile("./data/" + "technology" + ".db");
    ObjectContainer db4 = Db4o.openFile("./data/" + "dict" + ".db");
    ObjectContainer userDB = Db4o.openFile("./data/" + "user" + ".db");
    //Vector<String> resultVector=new Vector<String>();
    StringBuffer ret = new StringBuffer("");
    int allResult;
    

    public String queryData(int typeName, String keyword) {

        switch (typeName) {
            case 1:
            //ret = "1 未写入代码！";
            //break;
            case 2:
            //ret = "2 未写入代码！";
            //break;
            case 3:
            //ret = "3 未写入代码！";
            //break;
            case 4:
                DictDDL dict = new DictDDL();
                Query keywordquery = db4.query();
                keywordquery.constrain(DictDDL.class);
                Query explanationquery = keywordquery.descend("explanation");
                keywordquery.descend("word").constrain(keyword).like().or((explanationquery.constrain(keyword)).like());
                ObjectSet result = keywordquery.execute();
                System.out.println("result size: " + result.size());
                allResult = result.size();
                while (result.hasNext()) {
                    dict = (DictDDL) (result.next());
                    System.out.print(dict.getEntry() + "  ");
                    System.out.println(dict.getWord());
//                    System.out.println(dict.getPhonetic());
//                    System.out.println(dict.getExplanation());
//                    System.out.println(dict.getLevel());
                    ret.append(dict.getWord() + "\r\n" + "  [" + dict.getPhonetic() + "]" + "  " + dict.getExplanation() + "\r\n" + "  词典分类：" + dict.getLevel() + "\r\n");
                }

                break;
            case 5:
                break;
            case 6:
                UserDDL user = new UserDDL();
                Query usrquery=userDB.query();
                usrquery.descend("id").constrain(Integer.parseInt(keyword));
                ObjectSet usrResult=usrquery.execute();
                System.out.println("result size: "+usrResult.size());
                System.out.println("--------------------");
                while(usrResult.hasNext()){
                    user=(UserDDL)usrResult.next();
                    System.out.println("id:"+user.getId());
                    System.out.println("user:"+user.getUser());
                    System.out.println("password:"+user.getPassword());
                    System.out.println("Right:"+user.getRight());
                    System.out.println("Date:"+user.getRegistDate());
                    System.out.println("=================");
                }
                break;
            default:
                ret.append("error!");
        }
        userDB.close();
        db4.close();
        db3.close();
        db2.close();
        return ret.toString();
    }

    public boolean insertData(int typeName, String user, String password) {
        switch (typeName) {
            case 1:
            case 2:
            case 3:
            case 4:
            case 5:
            case 6:
                UserDDL userddl = new UserDDL();
                Query usrquery = userDB.query();
                usrquery.constrain(UserDDL.class);
                if (usrquery.descend("id").execute() == null) {
                    userddl.setId(0);
                } else {
                    ObjectSet result = usrquery.descend("id").execute();
                    while (result.hasNext()) {
                        userddl = (UserDDL) result.next();
                        System.out.println("userddl:"+userddl.getId());
                    }
                    userddl.setId(userddl.getId()+1);
                }
                    userddl.setUser(user);
                    userddl.setPassword(password);
                    userddl.setRegistDate(new Date().toString());
                    if(user.equals("root")||user.equals("lindily")){
                        userddl.setRight(0);
                    }else{
                        userddl.setRight(1);
                    }
                    userDB.store(userddl);
                    userDB.commit();
                    System.out.println("id:"+userddl.getId());
                    System.out.println("user:"+userddl.getUser());
                    System.out.println("password:"+userddl.getPassword());
                    System.out.println("Right:"+userddl.getRight());
                    System.out.println("Date:"+userddl.getRegistDate());
                    System.out.println("=================");
        }
        userDB.close();
        db4.close();
        db3.close();
        db2.close();
        return true;
    }

    public boolean deleteData(int typeName) {
        return true;
    }

    public String transData(int typeName) {

        switch (typeName) {
            case 1:
                ret.append("1 未写入代码！");
                break;
            case 2:
                ret.append("2 未写入代码！");
                break;
            case 3:
                ret.append("3 未写入代码！");
                break;
            case 4:
                try {
                    new com.mysql.jdbc.Driver();
                    String url = "jdbc:mysql://localhost:3306/siox_english";
                    java.sql.Connection conn = DriverManager.getConnection(url, "root", "111111");
                    java.sql.Statement stmt = conn.createStatement();
                    ResultSet rs = stmt.executeQuery("select * from english_vocabulary");
                    while (rs.next()) {
                        DictDDL dict = new DictDDL();
                        dict.setEntry(rs.getInt("entry"));
                        dict.setWord(rs.getString("word"));
                        dict.setPhonetic(rs.getString("phonetic"));
                        dict.setExplanation(rs.getString("explanation"));
                        dict.setLevel(rs.getString("level"));
                        db4.store(dict);
                    }
                    db4.commit();

                    ObjectSet os = db4.queryByExample(new DictDDL());
                    int i = 0;
                    while (os.hasNext()) {
                        DictDDL eng = new DictDDL();
                        eng = (DictDDL) os.next();
                        System.out.println(eng.getEntry() + " " + eng.getWord() + " " + eng.getPhonetic() + " " + eng.getExplanation() + " " + eng.getLevel());
                        i++;
                    }
                    System.out.println("===============================>tatal: " + i);
                    rs.close();
                    stmt.close();
                    conn.close();
                } catch (SQLException e) {
                    System.out.println("数据库出错!");
                }
                ret.append("倒入数据库成功！");
                break;
            case 5:
                break;
            case 6:
                break;
            default:
                ret.append(":( 抱歉，故障！");
        }
        userDB.close();
        db4.close();
        db3.close();
        db2.close();
        return ret.toString();
    }

    public String testTransData(int typeName, String keyword) {
        String resultTest = null;
        DictDDL dict = new DictDDL();
        Query keywordquery = db4.query();
        keywordquery.constrain(DictDDL.class);
        Query explanationquery = keywordquery.descend("explanation");
        keywordquery.descend("word").constrain(keyword).like().or((explanationquery.constrain(keyword)).like());
        ObjectSet result = keywordquery.execute();
        System.out.println("result size: " + result.size());
        allResult = result.size();
        while (result.hasNext()) {
            dict = (DictDDL) (result.next());
            System.out.print(dict.getEntry() + "  ");
            System.out.println(dict.getWord());
            resultTest = dict.getPhonetic();
        }
        userDB.close();
        db4.close();
        db3.close();
        db2.close();
        return resultTest;
    }

    public int getResult() {
        return this.allResult;
    }

    public static void main(String[] args) {
        DatabaseOperator dao = new DatabaseOperator();
        dao.insertData(6, "ccc","111");
//        dao.queryData(5, "1");
    }
}
